import sqlite3
import os
from datetime import datetime, timedelta
import logging
from typing import List, Dict, Any
import json

logger = logging.getLogger(__name__)


class DatabaseManager:
    def __init__(self, db_path: str = "test_cases.db"):
        self.db_path = db_path
        self.init_database()

    def init_database(self):
        """初始化数据库，创建表结构"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()

                # 创建测试用例表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS test_cases (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        case_id TEXT UNIQUE NOT NULL,
                        test_case_id TEXT,
                        title TEXT NOT NULL,
                        preconditions TEXT,
                        steps TEXT,
                        expected_results TEXT,
                        priority TEXT DEFAULT 'P3',
                        test_type TEXT DEFAULT '功能',
                        project_name TEXT,
                        module_name TEXT,
                        requirement TEXT,
                        status TEXT DEFAULT '待执行',
                        creator TEXT DEFAULT 'AI生成',
                        created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                ''')

                # 检查并添加test_case_id字段（如果不存在）
                cursor.execute("PRAGMA table_info(test_cases)")
                columns = [column[1] for column in cursor.fetchall()]
                if 'test_case_id' not in columns:
                    cursor.execute(
                        'ALTER TABLE test_cases ADD COLUMN test_case_id TEXT')
                    logger.info("添加test_case_id字段到test_cases表")

                # 创建配置表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS system_config (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        config_key TEXT UNIQUE NOT NULL,
                        config_value TEXT,
                        description TEXT,
                        created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                ''')

                # 创建数据清理日志表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS cleanup_logs (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        cleanup_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        deleted_count INTEGER DEFAULT 0,
                        status TEXT DEFAULT 'success',
                        message TEXT
                    )
                ''')

                # 插入默认配置
                self._insert_default_configs(cursor)

                conn.commit()
                logger.info("数据库初始化完成")

        except Exception as e:
            logger.error(f"数据库初始化失败: {str(e)}")
            raise

    def _insert_default_configs(self, cursor):
        """插入默认配置信息"""
        default_configs = [
            ('data_retention_days', '1', '数据保留天数'),
            ('auto_cleanup_enabled', 'true', '是否启用自动清理'),
            ('cleanup_time', '02:00', '清理时间（24小时制）'),
            ('max_test_cases_per_project', '1000', '每个项目最大用例数'),
            ('default_priority', 'P2', '默认优先级'),
            ('default_test_type', '功能', '默认测试类型')
        ]

        for config in default_configs:
            cursor.execute('''
                INSERT OR IGNORE INTO system_config (config_key, config_value, description)
                VALUES (?, ?, ?)
            ''', config)

    def insert_test_case(self, test_case: Dict[str, Any]) -> int:
        """插入测试用例"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()

                # 处理步骤和预期结果
                steps_json = json.dumps(test_case.get(
                    'steps', []), ensure_ascii=False)
                expected_results_json = json.dumps(test_case.get(
                    'expected_results', []), ensure_ascii=False)

                # 生成用例ID（TEST_001格式）
                test_case_id = test_case.get('test_case_id')
                if not test_case_id:
                    test_case_id = self._generate_test_case_id(cursor)

                cursor.execute('''
                    INSERT INTO test_cases (
                        case_id, test_case_id, title, preconditions, steps, expected_results,
                        priority, test_type, project_name, module_name, requirement,
                        status, creator
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ''', (
                    test_case.get('id', ''),
                    test_case_id,
                    test_case.get('title', ''),
                    test_case.get('preconditions', ''),
                    steps_json,
                    expected_results_json,
                    test_case.get('priority', 'P3'),
                    test_case.get('test_type', '功能'),
                    test_case.get('project_name', ''),
                    test_case.get('module_name', ''),
                    test_case.get('requirement', ''),
                    test_case.get('status', '待执行'),
                    test_case.get('creator', 'AI生成')
                ))

                conn.commit()
                return cursor.lastrowid

        except Exception as e:
            logger.error(f"插入测试用例失败: {str(e)}")
            raise

    def _generate_test_case_id(self, cursor) -> str:
        """生成用例ID，格式为test_001"""
        try:
            # 获取当前最大的用例ID编号
            cursor.execute('''
                SELECT test_case_id FROM test_cases
                WHERE test_case_id LIKE 'test_%'
                ORDER BY CAST(SUBSTR(test_case_id, 6) AS INTEGER) DESC
                LIMIT 1
            ''')
            result = cursor.fetchone()

            if result:
                # 提取编号并加1
                last_id = result[0]
                last_number = int(last_id.split('_')[1])
                new_number = last_number + 1
            else:
                # 如果没有记录，从1开始
                new_number = 1

            return f"test_{new_number:03d}"

        except Exception as e:
            logger.warning(f"生成用例ID失败，使用默认值: {str(e)}")
            return "test_001"

    def generate_batch_test_case_ids(self, count: int) -> list:
        """批量生成用例ID，确保从test_001开始连续编号"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()

                # 获取当前最大的用例ID编号
                cursor.execute('''
                    SELECT test_case_id FROM test_cases
                    WHERE test_case_id LIKE 'test_%'
                    ORDER BY CAST(SUBSTR(test_case_id, 6) AS INTEGER) DESC
                    LIMIT 1
                ''')
                result = cursor.fetchone()

                if result:
                    # 提取编号并从下一个开始
                    last_id = result[0]
                    start_number = int(last_id.split('_')[1]) + 1
                else:
                    # 如果没有记录，从1开始
                    start_number = 1

                # 生成连续的ID列表
                test_case_ids = []
                for i in range(count):
                    test_case_ids.append(f"test_{start_number + i:03d}")

                return test_case_ids

        except Exception as e:
            logger.warning(f"批量生成用例ID失败，使用默认值: {str(e)}")
            # 返回默认的ID列表
            return [f"test_{i+1:03d}" for i in range(count)]

    def get_test_cases(self, page: int = 1, page_size: int = 10,
                       search: str = None, project: str = None,
                       module: str = None, category: str = None,
                       priority: str = None) -> Dict[str, Any]:
        """获取测试用例列表，支持分页和搜索"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()

                # 构建查询条件
                where_conditions = []
                params = []

                if search:
                    where_conditions.append(
                        "(title LIKE ? OR requirement LIKE ?)")
                    params.extend([f'%{search}%', f'%{search}%'])

                if project:
                    where_conditions.append("project_name LIKE ?")
                    params.append(f'%{project}%')

                if module:
                    where_conditions.append("module_name LIKE ?")
                    params.append(f'%{module}%')

                if category:
                    where_conditions.append("test_type LIKE ?")
                    params.append(f'%{category}%')

                if priority:
                    where_conditions.append("priority = ?")
                    params.append(priority)

                where_clause = " AND ".join(
                    where_conditions) if where_conditions else "1=1"

                # 获取总数
                cursor.execute(
                    f"SELECT COUNT(*) FROM test_cases WHERE {where_clause}", params)
                total = cursor.fetchone()[0]

                # 获取分页数据，按用例ID排序
                offset = (page - 1) * page_size
                cursor.execute(f'''
                    SELECT * FROM test_cases
                    WHERE {where_clause}
                    ORDER BY
                        CASE
                            WHEN test_case_id LIKE 'test_%' THEN CAST(SUBSTR(test_case_id, 6) AS INTEGER)
                            WHEN test_case_id LIKE 'TEST_%' THEN CAST(SUBSTR(test_case_id, 6) AS INTEGER)
                            ELSE 999999
                        END ASC,
                        test_case_id ASC
                    LIMIT ? OFFSET ?
                ''', params + [page_size, offset])

                rows = cursor.fetchall()
                columns = [description[0]
                           for description in cursor.description]

                # 转换为字典列表
                test_cases = []
                for row in rows:
                    case_dict = dict(zip(columns, row))
                    # 解析JSON字段
                    if case_dict.get('steps'):
                        try:
                            case_dict['steps'] = json.loads(case_dict['steps'])
                        except (json.JSONDecodeError, TypeError):
                            # 如果解析失败，保持原始字符串
                            pass
                    # expected_results字段通常是字符串，不需要JSON解析
                    test_cases.append(case_dict)

                return {
                    "total": total,
                    "page": page,
                    "page_size": page_size,
                    "test_cases": test_cases
                }

        except Exception as e:
            logger.error(f"获取测试用例失败: {str(e)}")
            raise

    def get_test_case_by_id(self, case_id: str) -> Dict[str, Any]:
        """根据case_id或test_case_id获取单个测试用例详情"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                # 先尝试用case_id查询
                cursor.execute(
                    "SELECT * FROM test_cases WHERE case_id = ?", (case_id,))
                row = cursor.fetchone()

                # 如果没找到，尝试用test_case_id查询
                if not row:
                    cursor.execute(
                        "SELECT * FROM test_cases WHERE test_case_id = ?", (case_id,))
                    row = cursor.fetchone()

                if not row:
                    return None

                columns = [description[0]
                           for description in cursor.description]
                case_dict = dict(zip(columns, row))

                # 解析JSON字段
                if case_dict.get('steps'):
                    try:
                        case_dict['steps'] = json.loads(case_dict['steps'])
                    except (json.JSONDecodeError, TypeError):
                        # 如果解析失败，保持原始字符串
                        pass
                # expected_results字段通常是字符串，不需要JSON解析

                return case_dict

        except Exception as e:
            logger.error(f"获取测试用例详情失败: {str(e)}")
            raise

    def delete_test_case(self, case_id: str) -> bool:
        """删除测试用例，支持case_id或test_case_id"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                # 先尝试用case_id删除
                cursor.execute(
                    "DELETE FROM test_cases WHERE case_id = ?", (case_id,))
                if cursor.rowcount > 0:
                    conn.commit()
                    return True

                # 如果没有删除成功，尝试用test_case_id删除
                cursor.execute(
                    "DELETE FROM test_cases WHERE test_case_id = ?", (case_id,))
                conn.commit()
                return cursor.rowcount > 0

        except Exception as e:
            logger.error(f"删除测试用例失败: {str(e)}")
            raise

    def delete_all_test_cases(self) -> int:
        """删除所有测试用例"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT COUNT(*) FROM test_cases")
                count = cursor.fetchone()[0]
                cursor.execute("DELETE FROM test_cases")
                conn.commit()
                return count

        except Exception as e:
            logger.error(f"删除所有测试用例失败: {str(e)}")
            raise

    def get_test_cases_count(self) -> int:
        """获取测试用例总数"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT COUNT(*) FROM test_cases")
                count = cursor.fetchone()[0]
                return count

        except Exception as e:
            logger.error(f"获取测试用例总数失败: {str(e)}")
            return 0

    def cleanup_old_data(self) -> Dict[str, Any]:
        """清理旧数据"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()

                # 获取保留天数配置
                cursor.execute(
                    "SELECT config_value FROM system_config WHERE config_key = 'data_retention_days'")
                retention_days = int(cursor.fetchone()[0])

                # 计算清理日期
                cleanup_date = datetime.now() - timedelta(days=retention_days)

                # 删除旧数据
                cursor.execute(
                    "DELETE FROM test_cases WHERE created_time < ?", (cleanup_date,))
                deleted_count = cursor.rowcount

                # 记录清理日志
                cursor.execute('''
                    INSERT INTO cleanup_logs (deleted_count, status, message)
                    VALUES (?, ?, ?)
                ''', (deleted_count, 'success', f'清理了{deleted_count}条旧数据'))

                conn.commit()

                logger.info(f"数据清理完成，删除了{deleted_count}条旧数据")
                return {
                    "deleted_count": deleted_count,
                    "cleanup_date": cleanup_date,
                    "status": "success"
                }

        except Exception as e:
            logger.error(f"数据清理失败: {str(e)}")
            # 记录失败日志
            try:
                with sqlite3.connect(self.db_path) as conn:
                    cursor = conn.cursor()
                    cursor.execute('''
                        INSERT INTO cleanup_logs (deleted_count, status, message)
                        VALUES (?, ?, ?)
                    ''', (0, 'failed', str(e)))
                    conn.commit()
            except:
                pass
            raise

    def get_config(self, config_key: str) -> str:
        """获取配置值"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "SELECT config_value FROM system_config WHERE config_key = ?", (config_key,))
                result = cursor.fetchone()
                return result[0] if result else None

        except Exception as e:
            logger.error(f"获取配置失败: {str(e)}")
            return None

    def update_config(self, config_key: str, config_value: str) -> bool:
        """更新配置值"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    UPDATE system_config 
                    SET config_value = ?, updated_time = CURRENT_TIMESTAMP
                    WHERE config_key = ?
                ''', (config_value, config_key))
                conn.commit()
                return cursor.rowcount > 0

        except Exception as e:
            logger.error(f"更新配置失败: {str(e)}")
            raise


# 全局数据库管理器实例
db_manager = DatabaseManager()
